﻿#define MySql
#undef MSSql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class Leads : WebList_SXD
    {
        
        /// <summary>
        /// 网页调用时，读出前一次页的 search 条件，设定相应的控件。
        /// Session["Search_Leads"]) 在 Bt_Search_Click 函数中设定
        /// </summary>
        protected void Get_Search_Session()
        {
            string sSearch_Leads = Convert.ToString(Session["Search_Leads"]).Trim();
            if (sSearch_Leads != "")
            {
                string[] temp = sSearch_Leads.Split(',');

                Common_SXD.SetSelectedIndex(temp[0].Trim(), DDList_Office);
                Common_SXD.SetSelectedIndex(temp[1].Trim(), DDList_Operator);
                TextBox_Date_start_Register.Text = temp[2].Trim();
                TextBox_Date_end_Register.Text = temp[3].Trim();
                TextBox_SearchValue.Text = temp[4].Trim();
                TextMax_Lines.Text = temp[5].Trim();

                Common_SXD.SetSelected_RadioButtonList(temp[6].Trim(), RadioButton_OrderBy);

            }

        }

        /// <summary>
        /// 生成 sql 查询语句的 order by 部分
        /// </summary>
        /// <returns>返回Order by 字符串，如果出错，返回一个空格。</returns>
        protected string Get_OrderBy_FromSession(string myOrder_by)
        {
            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Leads"]).Trim();
            if (sOrder_by_Leads != "")
                return "order by " + sOrder_by_Leads + " ";
            else
                return "order by " + myOrder_by + " ";

        }

        private static string static_sOrder_by_Leads;

        protected void Page_Load(object sender, EventArgs e)
        {
            // 操作权限的判断
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_G_M = Convert.ToBoolean(Session["Is_G_M"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);

            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            string Name_Email = Session["Email"].ToString().Trim().ToLower();

            if (!bIs_O_M && !bIs_Admin && !bIs_Receptionist && !bIs_Counsellor && !bIs_Frontline_Agent
                && !bIs_Marketing)
            { //只有地区经理、系统管理员、前台、顾问、市场专员和客服专员可以进入
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，请用其他身份登录！");
                Response.Write("</fong></h2>");
                Response.End();
            }


            ///////////// 读出网页调用时带入的 order by信息，设定 ASC DESC 后，存入session["Order_by_Leads"]
            ///存入 order by 到 session 之中
            static_sOrder_by_Leads = Request["Order_by_Leads"];
            string sLast_Order_by_Leads = Convert.ToString(Session["Order_by_Leads"]).Trim();
            if (static_sOrder_by_Leads == "" || static_sOrder_by_Leads == null) ///没有order by 信息输入
            {
                Session["Order_by_Leads"] = "";
            }
            else
            {
                if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "ASC")
                    Session["Order_by_Leads"] = static_sOrder_by_Leads + " ASC";
                else if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DESC")
                    Session["Order_by_Leads"] = static_sOrder_by_Leads + " DESC";
                else
                    Session["Order_by_Leads"] = "";

            }


            if (!IsPostBack)
            {
                Common_SXD.Kill_Sleep_Threads(Response);
                TextMax_Lines.Text = "78";
                Init_Term_Search();

                if (bIs_Admin || bIs_G_M /* Name_Email == "christine_du" ||
                   Name_Email == "judy_yao" || Name_Email == "lmz"*/
                    || ( bIs_Marketing && Session["Office"].ToString().Trim() == "上海" ))
                {
                    DDList_Office.Enabled = true;
                    DDList_Office.Visible = true;
                    Common_SXD.Init_Office(DDList_Office);
                    Get_Search_Session();

                    string sOffice = DDList_Office.SelectedItem.Text;
                    Init_Table_Leads(sOffice);

                }
                else
                {
                    DDList_Office.Visible = true;
                    Common_SXD.Init_Office(DDList_Office);
                    Get_Search_Session();

                    string temp = Session["Office"].ToString().Trim();
                    Common_SXD.SetSelectedIndex(temp, DDList_Office);
                    DDList_Office.Enabled = false;
                    Init_Table_Leads(temp);

                }
                ///清空全部统计功能选择标记
                ///
                CheckBox_Channel_Customer2Know.Checked = false;
                CheckBox_Office_Statistic.Checked = false;
                CheckBox_Type_Contact.Checked = false;
                CheckBox_Overseas_Study.Checked = false;
                CheckBox_State_Customer.Checked = false;
                CheckBox_Second_State_Customer.Checked = false;

                Off_Display_Controls_Statistics();
                ///Init_Select_Statistics();

            }
            
        }

        protected void Bt_Search_Click(object sender, EventArgs e)
        {   ///string sBClassID = "";
            string sBClassID = Session["Office"].ToString().Trim();
            if (DDList_Office.Enabled)
                sBClassID = DDList_Office.SelectedItem.Text;
            if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT")
                static_sOrder_by_Leads = "";

            ///保存 session["Search_Leads"]
            ///
            string sSearch_Leads = sBClassID + ", ";
            sSearch_Leads += DDList_Operator.SelectedItem.Text.Trim() + ", ";
            sSearch_Leads += TextBox_Date_start_Register.Text.Trim() + ", ";
            sSearch_Leads += TextBox_Date_end_Register.Text.Trim() + ", ";
            sSearch_Leads += TextBox_SearchValue.Text.Trim() + ", ";
            sSearch_Leads += TextMax_Lines.Text.Trim() + ", ";
            sSearch_Leads += RadioButton_OrderBy.SelectedItem.Text.ToString().Trim() + ", ";

            Session["Search_Leads"] = sSearch_Leads;
            
            Init_Table_Leads(sBClassID);
            
        }

        /*
        /// <summary>
        /// 统计报表用选择菜单的初始化
        /// </summary>
        private void Init_Select_Statistics()
        {           SqlConnection sqlCnn = new SqlConnection(strCnn);
                // 载入客户留学意向表，绑定到 Drop_Overseas_Study 中
                string sSql = "select * from Overseas_Study  order by Code_Overseas_Study asc";
                SqlDataAdapter sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                DataSet bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "Overseas_Study");
                Drop_Overseas_Study.DataSource = bsDataSet.Tables["Overseas_Study"];
                Drop_Overseas_Study.DataBind();
                sqlCnn.Close();

                //sqlCnn = new SqlConnection(strCnn);
                // 载入客户接触方式表，绑定到 Drop_Type_Contact 中
                sSql = "select * from Type_Contact order by Code_Type_Contact asc";
                sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "Type_Contact");
                Drop_Type_Contact.DataSource = bsDataSet.Tables["Type_Contact"];
                Drop_Type_Contact.DataBind();
                sqlCnn.Close();

                Common_SXD.Init_Office(Drop_Office_Statistic);

                // 载入客户了解渠道表，绑定到 Drop_Channel_Customer2Know 中
                sSql = "select * from Channel_Customer2Know order by Code_Channel_Customer2Know asc";
                sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "Channel_Customer2Know");
                Drop_Channel_Customer2Know.DataSource = bsDataSet.Tables["Channel_Customer2Know"];
                Drop_Channel_Customer2Know.DataBind();
                sqlCnn.Close();

                // 载入客户状态表，绑定到 Drop_State_Customer 中
                // 载入二次分配客户状态表，绑定到 Drop_Second_State_Customer 中
                sSql = "select * from State_Customer order by Code_State_Customer asc";
                sqlDA = new SqlDataAdapter(sSql, sqlCnn);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                bsDataSet = new DataSet();
                sqlDA.Fill(bsDataSet, "State_Customer");
                Drop_State_Customer.DataSource = bsDataSet.Tables["State_Customer"];
                Drop_State_Customer.DataBind();

                Drop_Second_State_Customer.DataSource = bsDataSet.Tables["State_Customer"];
                Drop_Second_State_Customer.DataBind();
                sqlCnn.Close();

        }*/

        private void Off_Display_Controls_Statistics()
        {
            CheckBox_Channel_Customer2Know.Visible = false;
            ///Drop_Channel_Customer2Know.Visible = false;
            CheckBox_Office_Statistic.Visible = false;
            ///Drop_Office_Statistic.Visible = false;
            CheckBox_Type_Contact.Visible = false;
            ///Drop_Type_Contact.Visible = false;
            CheckBox_Overseas_Study.Visible = false;
            ///Drop_Overseas_Study.Visible = false;
            CheckBox_State_Customer.Visible = false;
            ///Drop_State_Customer.Visible = false;
            CheckBox_Second_State_Customer.Visible = false;
            ///Drop_Second_State_Customer.Visible = false;
            ///

            
        }

        private void Init_Term_Search()
        {
            TextBox_Date_start_Register.Text = Common_SXD.startDate_CurrentMonth();
            TextBox_Date_end_Register.Text = Common_SXD.TimeToYMD(DateTime.Now.ToString());
        }

        /// <summary>
        /// 显示统计表
        /// </summary>
        private void Display_Statistics(string myOffice, 
            string xTitle, string xName_Control, string xField_Order,
            string yTitle, string yName_Control, string yField_Order)
        {
            string sSearchValue = TextBox_SearchValue.Text.Trim();
            DateTime dNow = DateTime.Now;
            string[] ColunmName_Staitstics;
            string[] ColunmValue_Staitstics;
            string RowName_Staitstics = "";

            Label_Total.Text = "“" + Common_SXD.English_Chinese(yTitle) + "” 与 “" + Common_SXD.English_Chinese(xTitle) +"” 统计表";
            Common_SXD.Kill_Sleep_Threads(Response);
            
            /// X轴台头 
            string sSql = "select " + xTitle + " from " + xName_Control + " order by " + xField_Order + " ";
            //SqlConnection sqlCnn = new SqlConnection();
            //SqlDataReader sqlReader = Common_SXD.Open_SqlDataReader(sSql, sqlCnn, "OK", "打开数据库出错：", Response);
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
            // 读入第一个查询结果集， 即 Contract 中的数据
            ///int nCol = sqlReader.  .FieldCount;
            
            //清除表中的内容
            Table_Leads.Rows.Clear();
            int nCol = 0;
            while (MysqlReader.Read())
            {
                nCol++;
            }
            ColunmName_Staitstics = new string[nCol];
            ColunmValue_Staitstics = new string[nCol];

            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Clone();
            ///SqlConnection sqlCnn = new SqlConnection();
            ///SqlDataReader sqlReader = Common_SXD.Open_SqlDataReader(sSql, sqlCnn, "OK", "打开数据库出错：", Response);
            ///
            MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
            nCol = 0;
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                // Create a new cell and add it to the Cells collection.
                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl("  "));
                row.Cells.Add(cell);
                while (MysqlReader.Read())
                {
                    // Create a new cell and add it to the Cells collection.
                    cell = new HtmlTableCell();
                    ColunmName_Staitstics[nCol] = MysqlReader.GetName(0).ToString();
                    ColunmValue_Staitstics[nCol] = MysqlReader.GetValue(0).ToString();
                    cell.Controls.Add(new LiteralControl(ColunmValue_Staitstics[nCol].Trim()));
                    row.Cells.Add(cell);
                    nCol++;
                }
                
                Table_Leads.Rows.Add(row);
                row.BgColor = "Tan";
                
            }
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Clone();
            /// Y轴台头 
            /// sSql = "select Channel_Customer2Know  from Channel_Customer2Know order by Code_Channel_Customer2Know";
            sSql = "select " + yTitle + " from " + yName_Control + " order by " + yField_Order + " ";
            //SqlConnection sqlCnn = new SqlConnection();
            //SqlDataReader sqlReader = Common_SXD.Open_SqlDataReader(sSql, sqlCnn, "", "打开数据库出错：", Response);
            MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
            
            // 依次读出数据
            int Row = 0;
            while (MysqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sLeftTitle = MysqlReader.GetValue(0).ToString().Trim();
                ///ColunmValue_Staitstics = sLeftTitle;
                RowName_Staitstics = MysqlReader.GetName(0).ToString().Trim(); 
                // Provide a different background color for alternating rows.
                if (Row % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    /*
                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReadOnly:" +
                        sNo_Series_Lead.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series_Lead.Trim() +
                        "</a>"));*/
                    cell.Controls.Add(new LiteralControl(sLeftTitle.Trim()));

                    row.Cells.Add(cell);
                }

                for (int Colunm = 0; Colunm < nCol; Colunm++)
                {
                    // 计算统计结果
                    HtmlTableCell cell = new HtmlTableCell();
                    /// 获取where 右侧， 
                    /// 
                    string sFilter = Filter_Search_Leads(myOffice);
                    string[] strArray = Regex.Split(sFilter, "where");
                    sFilter = strArray[1] + " ";
                    if (ColunmValue_Staitstics[Colunm] .Trim() != "总部")
                        sFilter +=" and " + ColunmName_Staitstics[Colunm] + " like '" + ColunmValue_Staitstics[Colunm] + "' ";
                    if (sLeftTitle.Trim() != "总部")
                        sFilter +=" and " + RowName_Staitstics + " like '" + sLeftTitle + "'";
                    //记录个数
                    long i = 0;
                    string temp;
                    try
                    {
#if MySql
                        temp = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No_Lead) from Leads",
                            sFilter, Response);
#elif MSSql
                        temp = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No_Lead) from Leads",
                            sFilter, Response);
#endif
                        i = Convert.ToInt32(temp) - 1;
                        temp = i.ToString().Trim();
                    }
                    catch (System.Exception e)
                    {
                        temp = e.Message;
                    }
                    ///string temp = "Row=" + Row.ToString() + "   Colunm=" + Colunm.ToString();
                    ///temp += sLeftTitle + ColunmValue_Staitstics[Colunm];
                    ///cell.Controls.Add(new LiteralControl(""));
                    cell.Controls.Add(new LiteralControl(temp));
                    row.Cells.Add(cell);
                }

                Table_Leads.Rows.Add(row);
                Row++;
            }
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Clone(); // 关闭数据库连接



        }

        /// <summary>
        /// 设定客户表查询的过滤条件
        /// </summary>
        private string Filter_Search_Leads(string myOffice)
        {
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);

            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Leads"]);

            DateTime dNow = DateTime.Now;

            ///Label_Total.Text = "";
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            // 包含2个 Select 语句的 SQL，能返回2个结果集
            string sSql = "select top 1 Series_No_Lead from Leads where Series_No_Lead like '-99' and ";
            */
            string sSql_Search = "";
            /* if (bIs_Admin )
             {
             }
             else */
            int tDay = DateTime.Now.Day;
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year - 1;

            string sDay_Minus1Year = Convert.ToString(tYear) + "-" + Convert.ToString(tMonth) + "-" + Convert.ToString(tDay);

            if (sDDList_Operator == "前台" && ( bIs_Receptionist || bIs_Admin ) )
            {
                ///sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";
                ///前台可以看到当天登记的客户数据
                ///
                if (DDList_Office.Enabled)
                { /// 具有前台权限的管理员,可以看到各地前台当天登记的客户数据
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else ///前台可以看到当天登记的客户数据
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                string sToday = dNow.ToString().Trim();
                sSql_Search += "Date_Register = '" + Common_SXD.TimeToYMD(sToday) + " ' and ";
                /// sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and */ ";

            }
            else if (sDDList_Operator == "客服" && bIs_Frontline_Agent)
            {

                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                ///客户状态0无效， 二次顾问未分配二个条件同时成立，进入客服清单。
                ///
                sSql_Search += "( ( ( State_Customer like '0无效'  /* or  Counsellor like '%无联系方式%' */ ) and ";
                sSql_Search += "( Second_Counsellor like '' or Second_Counsellor is null ) ) or ";

                ///或者有效客户30天未签约，超过延期未签约，而且没有分配二次顾问，进入客服清单。
                ///
                sSql_Search += "( Date_Register < '" + Common_SXD.theDayBefore(29) + " ' and ";
                sSql_Search += "Date_Continue_Follow_up < '" + Common_SXD.theDayBefore(0) + " ' and ";
                sSql_Search += " ( Series_No like '' or Series_No is null ) )  and ";
                sSql_Search += " ( Second_Counsellor like '' or Second_Counsellor is null )) and ";

                ///回访日期是今天或者0无效
                ///
                ///sSql += " ( Date_FA_Return_Visit < '1930-1-1' or Date_FA_Return_Visit = '" + Common_SXD.TimeToYMD(DateTime.Now.ToString()) + "' ) and ";

                sSql_Search += "Is_Active = 1 and (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and ";
                sSql_Search += "Counsellor not like '%无联系方式%' and ";   
            }
            else if (sDDList_Operator == "顾问" && bIs_Counsellor)
            {
                ///顾问可以打开“已确认资金流向表”
                Common_SXD.Init_Table_CheckedCashFlow(Table_CheckedCashFlow);

                sSql_Search += " where  Office = '" + Session["Office"].ToString().Trim() + "' and ";
                ///
                sSql_Search += " ( Series_No like '' or Series_No is null ) and ";
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";

                ///一次分配的顾问，要求30天内签约，或者在延续跟进日期之内。
                sSql_Search += "( ( Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += "( Date_Register > '" + Common_SXD.theDayBefore(29) + " ' or ";
                sSql_Search += "Date_Continue_Follow_up > '" + Common_SXD.theDayBefore(0) + " ') and ";
                sSql_Search += "( State_Customer is null  or State_Customer like '' or State_Customer like '1星潜在' or State_Customer like '有效' or State_Customer like '5星客户' or State_Customer like '4星客户' or State_Customer like '3星客户') ) or ";
                /*
                /// 或者是二次分配的顾问
                sSql += " ( Second_Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql += " ( Second_State_Customer is null or Second_State_Customer like '' or Second_State_Customer like '1星潜在'  or ";
                sSql += "  Second_State_Customer like '有效' or Second_State_Customer like '5星客户' or Second_State_Customer like '4星客户' or Second_State_Customer like '3星客户' ) ) ) and ";
                */

                /// 或者是二次分配的顾问,在一次分配的顾问确认0无效的情况下
                sSql_Search += " ( Second_Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += " State_Customer like '%0无效%' and ";
                sSql_Search += " Second_State_Customer not like '%0无效%' ) or ";
                
                /// 或者是二次分配的顾问,在一次分配的顾问有效期之外
                sSql_Search += " ( Second_Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += " State_Customer not like '%0无效%' and ";
                sSql_Search += "( Date_Register <= '" + Common_SXD.theDayBefore(29) + " ' and ";
                sSql_Search += "Date_Continue_Follow_up <= '" + Common_SXD.theDayBefore(0) + " ')  and ";
                sSql_Search += " ( Second_State_Customer is null or Second_State_Customer like '' or Second_State_Customer like '1星潜在'  or ";
                sSql_Search += "  Second_State_Customer like '有效' or Second_State_Customer like '5星客户' or Second_State_Customer like '4星客户' or Second_State_Customer like '3星客户' ) ) ) and ";
                /// 有效数据
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and */ ";

            }
            else if (sDDList_Operator == "一次顾问" && bIs_Counsellor)
            {
                ///顾问可以打开“已确认资金流向表”
                Common_SXD.Init_Table_CheckedCashFlow(Table_CheckedCashFlow);

                sSql_Search += " where  Office = '" + Session["Office"].ToString().Trim() + "' and ";
                ///
                sSql_Search += " ( Series_No like '' or Series_No is null ) and ";
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";

                ///一次分配的顾问，要求30天内签约，或者在延续跟进日期之内。
                sSql_Search += "( Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += "( Date_Register > '" + Common_SXD.theDayBefore(29) + " ' or ";
                sSql_Search += "Date_Continue_Follow_up > '" + Common_SXD.theDayBefore(0) + " ') and ";
                sSql_Search += "( State_Customer is null  or State_Customer like '' or State_Customer like '1星潜在' or State_Customer like '有效' or State_Customer like '5星客户' or State_Customer like '4星客户' or State_Customer like '3星客户') ) and ";

                /// 未签约
                sSql_Search += " Date_First_Payment <= '1930-1-1' and ";
                /// 有效数据
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and */ ";

            }
            else if (sDDList_Operator == "二次顾问" && bIs_Counsellor)
            {
                ///顾问可以打开“已确认资金流向表”
                Common_SXD.Init_Table_CheckedCashFlow(Table_CheckedCashFlow);

                sSql_Search += " where  Office = '" + Session["Office"].ToString().Trim() + "' and ";
                ///
                sSql_Search += " ( Series_No like '' or Series_No is null ) and ";
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";

                /// 或者是二次分配的顾问,在一次分配的顾问确认0无效的情况下
                sSql_Search += " ( Second_Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += " State_Customer like '%0无效%' and ";
                sSql_Search += " Second_State_Customer not like '%0无效%' ) or ";

                /// 或者是二次分配的顾问,在一次分配的顾问有效期之外
                sSql_Search += " ( Second_Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
                sSql_Search += " State_Customer not like '%0无效%' and ";
                sSql_Search += " ( Date_Register <= '" + Common_SXD.theDayBefore(29) + " ' and ";
                sSql_Search += "Date_Continue_Follow_up <= '" + Common_SXD.theDayBefore(0) + " ' ) and ";
                sSql_Search += " ( Second_State_Customer is null or Second_State_Customer like '' or Second_State_Customer like '1星潜在'  or ";
                sSql_Search += "  Second_State_Customer like '有效' or Second_State_Customer like '5星客户' or Second_State_Customer like '4星客户' or Second_State_Customer like '3星客户' ) )  and ";
                
                /// 未签约
                sSql_Search += " Date_First_Payment <= '1930-1-1' and ";
                /// 有效数据
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and */ ";

            }
            else if (sDDList_Operator == "延续跟进" && bIs_O_M)
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                /// 延续跟进日期
                /// 有效+1月内未签约，但顾问表示仍然要继续跟进等待签约的客户，地区经理手动输入下次询问顾问进展的日期。此日期内，不再进入客服页面；过期，自动跳入客服页面
                /// 登记日期 小于（今天 - 30 天）
                sSql_Search += " ( State_Customer is null  or State_Customer like '' or State_Customer like '有效' or State_Customer like '5星客户' or State_Customer like '4星客户' or State_Customer like '3星客户') and ";


                sSql_Search += "Date_Register < '" + Common_SXD.theDayBefore(29) + " ' and ";
                ///sSql += "Date_Continue_Follow_up > '" + Common_SXD.theDayBefore(0) + " ' and ";
                sSql_Search += " ( Series_No like '' or Series_No is null ) and ";

                ///sSql += "Is_Active = 1 and ";
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%')  and */ ";


            }
            else if ((sDDList_Operator == "地区经理" && bIs_O_M)
                 || (sDDList_Operator == "市场专员" && bIs_Marketing )
              ///|| (sDDList_Operator == "市场专员" && bIs_Marketing && Session["Office"].ToString().Trim() == "上海")
            ) ///市场专员只能在上海办公室登录！
            {

                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                ///sSql += "Counsellor not like '%无联系方式%' and ";
                ///sSql += "Is_Active = 1  and (Second_State_Customer is null or Second_State_Customer not like '%0无效%') and ";

                /// 地区经理
                sSql_Search += " Is_Active = 1  and ";
                sSql_Search += "( Counsellor not like '%无联系方式%' /* or Second_State_Customer is null or Second_State_Customer not like '%0无效%' */) and ";

            }
            else if (sDDList_Operator == "有效且非名单" && (bIs_O_M || bIs_Marketing))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                ///sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);


                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1 and ";
                ///sSql_Search += "( State_Customer like '%有效%' or State_Customer like '5星客户' or State_Customer like '4星客户' or State_Customer like '3星客户') and ";
                sSql_Search += " State_Customer not like '%0无效%'  and ";
                sSql_Search += " Second_State_Customer not like '%0无效%' and ";
                sSql_Search += " Date_First_Payment <= '1930-1-1' and ";
                sSql_Search += " Type_Contact not like '名单（教育展、讲座、市场部批量）' and ";
            }
            else if (sDDList_Operator == "有效" && (bIs_O_M || bIs_Counsellor))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);


                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                sSql_Search += "Counsellor not like '%无联系方式%' and ";
                sSql_Search += "Is_Active = 1 and ";
                ///sSql_Search += "( State_Customer like '%有效%' or State_Customer like '5星客户' or State_Customer like '4星客户' or State_Customer like '3星客户') and ";
                sSql_Search += " State_Customer not like '%0无效%'  and ";
                sSql_Search += " Second_State_Customer not like '%0无效%' and ";
                sSql_Search += " Date_First_Payment <= '1930-1-1' and ";
            }
            else if (sDDList_Operator == "0无效" && (bIs_O_M || bIs_Counsellor))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);

                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                sSql_Search += "Is_Active = 1 and ";
                sSql_Search += "( State_Customer like '%0无效%' or  Counsellor like '%无联系方式%') and ";
            }
            else if (sDDList_Operator == "1星潜在" && bIs_O_M)
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                sSql_Search += " Counsellor not like '%无联系方式%' and  State_Customer not like '0无效'  and ";
                sSql_Search += "Is_Active = 1  and /* (Second_State_Customer is null or Second_State_Customer not like '%0无效%')  and */";

                sSql_Search += "State_Customer like '%1星潜在%' and ";
            }
            else if (sDDList_Operator == "已签约" && (bIs_O_M || bIs_Counsellor))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                ///sSql += "( Is_Active = 0 or Counsellor like '%无联系方式%' or (State_Customer like '%0无效%' and Second_State_Customer like '%0无效%')) and";
                sSql_Search += " Series_No not like '' and  Series_No is not null and Series_No not like '%授权%' and";
                sSql_Search += " Date_First_Payment > '1930-1-1' and ";
                if (!bIs_O_M)
                    sSql_Search += " Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
            }
            else if (sDDList_Operator == "已授权" && (bIs_O_M || bIs_Counsellor))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                ///sSql += "( Is_Active = 0 or Counsellor like '%无联系方式%' or (State_Customer like '%0无效%' and Second_State_Customer like '%0无效%')) and";
                sSql_Search += " Series_No like '%授权%' and ";
                sSql_Search += " Date_First_Payment > '1930-1-1' and ";
                if (!bIs_O_M)
                    sSql_Search += " Counsellor like '" + Session["C_N_Employee"].ToString().Trim() + "' and ";
            }
            else if (sDDList_Operator == "休眠" && (bIs_O_M || bIs_Marketing || bIs_Frontline_Agent))
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///
                sSql_Search += " Date_Register >= '" + TextBox_Date_start_Register.Text + "' and  Date_Register  <= '" + TextBox_Date_end_Register.Text + "' and ";
                /// sSql += "( Is_Active = 0 or Counsellor like '%无联系方式%' or Second_State_Customer like '%0无效%' ) and";

                /// 休眠
                sSql_Search += " ( Is_Active = 0  or ";
                sSql_Search += "  Counsellor like '%无联系方式%' ) and ";

            }
            /*
                        sSql += " Office = '" + Session["Office"] + "') and ";

                        if (bIs_Counsellor)
                            if (!bIs_O_M)
                                if (!bIs_Admin)
                                    if (!bIs_Collector)
                                        sSql += "( Counsellor = '" + Session["C_N_Employee"] + "' or  Writer = '" + Session["C_N_Employee"] + "' ) and ";

                        // 滤除其他年份的数据
                        int previousYear = Convert.ToInt16(Session["Year_Insert"]) - 1;
                        /// string s_priviousYear = Convert.ToString(previousYear).Trim();

                        sSql += "(  Series_No like '" + Session["Year_Insert"] + "%' or  Series_No like '" + Convert.ToString(previousYear).Trim() + "%') and ";
            */
            sSql_Search += "(  Student_Name  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Tel_Student  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Series_No_Lead  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Series_No  like '%" + sSearchValue + "%' ";

            sSql_Search += "or  Notes_Lead  like '%" + sSearchValue + "%' ";
            /*sSql += "or  Notes_Activity  like '%" + sSearchValue + "%' ";
            sSql += "or  Notes_Channel_C2K  like '%" + sSearchValue + "%' ";
            sSql += "or  Notes_Customer  like '%" + sSearchValue + "%' ";  
            sSql += "or  Notes_FA_Return_Visit  like '%" + sSearchValue + "%' ";
             * */

            sSql_Search += "or  Name_Inquirer  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Tel_Inquirer  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Email_Inquirer  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  QQMSN_Inquirer  like '%" + sSearchValue + "%' )";
                        
            return sSql_Search;
        }

        /// <summary>
        /// 客户信息列表
        /// </summary>
        private void List_Customers(string myOffice)
        {
            ///Common_SXD.set_Flag_Database(Flag_Database.MSSql);
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);

            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Leads"]);

            DateTime dNow = DateTime.Now;

            Label_Total.Text = "";
            /*
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象
            // 包含2个 Select 语句的 SQL，能返回2个结果集
             */ 
            string sSql = "select " + Top_MSSql("1") + "Series_No_Lead from Leads where Series_No_Lead like '-99' and ";

            int tDay = DateTime.Now.Day;
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year - 1;

            string sDay_Minus1Year = Convert.ToString(tYear) + "-" + Convert.ToString(tMonth) + "-" + Convert.ToString(tDay);

            if (sDDList_Operator == "前台" && bIs_Receptionist)
            {
                Session.Timeout = 118;
                Common_SXD.Init_Table_NewOne_Lead(Table_NewOne_Lead);
                sSql = "select" + Top_MSSql(TextMax_Lines.Text) + "Series_No_Lead, Office, Counsellor, Date_Register, Name_Inquirer, Overseas_Study,  Type_Contact, Date_Fresh_Return_Visit, Notes_Lead from Leads";
                
            }
            else if (sDDList_Operator == "客服" && bIs_Frontline_Agent)
            {
                Session.Timeout = 118;
                sSql = "select" + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Name_Frontline_Agent,Date_FA_Return_Visit, Second_State_Customer, Second_Counsellor,Date_Register, Name_Inquirer, Student_Name,  Overseas_Study, Date_Fresh_Return_Visit, Notes_Lead  from Leads";
            }
            else if ((sDDList_Operator == "顾问" || sDDList_Operator == "一次顾问" || sDDList_Operator == "二次顾问") && bIs_Counsellor)
            {
                ///顾问可以打开“已确认资金流向表”
                Common_SXD.Init_Table_CheckedCashFlow(Table_CheckedCashFlow);
                sSql = "select" + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Student_Name, Tel_Student, State_Customer, Date_First_Payment, Series_No, Date_Register, Name_Inquirer, Tel_Inquirer, Email_Inquirer, QQMSN_Inquirer, Overseas_Study, Date_Fresh_Return_Visit,  Type_Contact, Notes_Lead from Leads";
                
            }
            else if (sDDList_Operator == "延续跟进" && bIs_O_M)
            {
                sSql = "select " + Top_MSSql( TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Name_Inquirer, Overseas_Study, Type_Contact, Date_Continue_Follow_up, Date_Fresh_Return_Visit, Notes_Lead from Leads";
                
            }
            else if ((sDDList_Operator == "地区经理" && bIs_O_M)
                 || (sDDList_Operator == "市场专员" && bIs_Marketing )
              ///|| (sDDList_Operator == "市场专员" && bIs_Marketing && Session["Office"].ToString().Trim() == "上海")
            ) ///市场专员只能在上海办公室登录！
            {
                Session.Timeout = 118;
                sSql = "select" + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Date_First_Payment, Name_Inquirer, Student_Name, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up, /* Name_Frontline_Agent,*/ Date_FA_Return_Visit, Date_Fresh_Return_Visit, Notes_Lead from Leads";
                
            }
            else if (sDDList_Operator == "有效" && (bIs_O_M || bIs_Counsellor) )
            {
                sSql = "select " + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Name_Inquirer, Student_Name, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_First_Payment, Date_Fresh_Return_Visit  from Leads";

            }
            else if (sDDList_Operator == "0无效" && (bIs_O_M || bIs_Counsellor))
            {
                sSql = "select " + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Name_Inquirer, Date_Fresh_Return_Visit, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up,Notes_Lead from Leads";

            }
            else if (sDDList_Operator == "有效且非名单" && (bIs_O_M || bIs_Marketing))
            {
                sSql = "select " + Top_MSSql(TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Name_Inquirer, Student_Name, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know,/* Date_Continue_Follow_up,*/ Date_Fresh_Return_Visit  from Leads";

            }
            else if (sDDList_Operator == "1星潜在" && bIs_O_M)
            {
                sSql = "select " + Top_MSSql( TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up,Notes_Lead from Leads";

            }
            else if (sDDList_Operator == "已签约" && (bIs_O_M || bIs_Counsellor))
            {
                sSql = "select " + Top_MSSql( TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Series_No, Student_Name, Date_First_Payment, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up,Notes_Lead from Leads";

            }
            else if (sDDList_Operator == "已授权" && (bIs_O_M || bIs_Counsellor))
            {
                sSql = "select " + Top_MSSql( TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Series_No, Date_First_Payment, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up,Notes_Lead from Leads";
}
            else if (sDDList_Operator == "休眠" && (bIs_O_M || bIs_Marketing || bIs_Frontline_Agent) )
            {
                sSql = "select " + Top_MSSql( TextMax_Lines.Text) + " Series_No_Lead, Office, Counsellor, Second_Counsellor, Date_Register, Name_Inquirer, Overseas_Study, Type_Contact, State_Customer,  Second_State_Customer, Channel_Customer2Know, Date_Continue_Follow_up, Date_Fresh_Return_Visit, Notes_Lead, Is_Active from Leads";

            }

            /// 读取过滤条件
            sSql += Filter_Search_Leads(myOffice);

            /////排序方式设定开始
            if (sDDList_Operator == "前台" && bIs_Receptionist)
            {
                sSql += Get_OrderBy_FromSession("Office, Series_No_Lead DESC");
            }
            else if( sDDList_Operator == "休眠")
            {
                sSql += Get_OrderBy_FromSession("Is_Active DESC, Office , State_Customer, Counsellor, Series_No_Lead DESC");
            }
            else
            {
                sSql += Get_OrderBy_FromSession("Office , State_Customer DESC, Counsellor, Series_No_Lead DESC");
            }
            //////排序方式结束
            
#if MSSql
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
 
#elif MySql
            sSql += Limit_MySql(TextMax_Lines.Text);

            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            
#endif 
            /*
            switch (Common_SXD.get_Flag_Database())
            {
                case Flag_Database.MSSql:
                    {
                        sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
                        break;
                    }
                case Flag_Database.MySql:
                    {
                        MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
                        break;
                    }

            }
            */

            /// 获取where 右侧， order 左侧的字符串，存入 strArray[0]中。
            string[] strArray = Regex.Split(sSql, "where");
            sSql = strArray[1];
            strArray = Regex.Split(sSql, "order");

            //记录个数
            //string str = CHAR_SQL_PLUS1("select count(Series_No_Lead) from Leads",
            //    strArray[0], Response);
#if MySql
            //记录个数
            string str = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No_Lead) from Leads",
                strArray[0], Response);
#elif MSSql
            //记录个数
            string str = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No_Lead) from Leads",
                strArray[0], Response);
#endif

            Label_Total.Text = "共计:" + Convert.ToString(Convert.ToUInt64(str) - 1) + "条记录 ";


            // 读入第一个查询结果集， 即 Contract 中的数据
#if MSSql
            int nCol = sqlReader.FieldCount;
#elif MySql
            int nCol = MysqlReader.FieldCount;
#endif
            ///int nCol = 0; /// sqlReader.FieldCount;
                          /// 
           /* switch (Common_SXD.get_Flag_Database())
            {
                case Flag_Database.MSSql:
                    {
                        nCol = sqlReader.FieldCount;
                        break;
                    }
                case Flag_Database.MySql:
                    {
                        nCol = MysqlReader.FieldCount;
                        break;
                    }

            }*/
            //清除表中的内容
            Table_Leads.Rows.Clear();

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = "";
                    if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT") 
                    {
#if MSSql
                        temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                        temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                        /* switch (Common_SXD.get_Flag_Database())
                        {
                            case Flag_Database.MSSql:
                                {
                                    temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                                    break;
                                }
                            case Flag_Database.MySql:
                                {
                                    temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
                                    break;
                                }

                        }*/
                        
                    }
                    else
                    {
#if MSSql
                        if (static_sOrder_by_Leads == sqlReader.GetName(i))
                        {
                             temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                        }
                        else
                        {
                              temp = "<A href=\"./Leads.aspx?Order_by_Leads=" + sqlReader.GetName(i) + "\">";
                              temp += Common_SXD.English_Chinese(sqlReader.GetName(i));
                              temp += "</A>";
                        }
#elif MySql

                    temp = Common_SXD.Swicth_Click_Order_by(static_sOrder_by_Leads, MysqlReader.GetName(i));
#endif
                        /*
                        switch (Common_SXD.get_Flag_Database())
                        {
                            case Flag_Database.MSSql:
                                {
                                    if (static_sOrder_by_Leads == sqlReader.GetName(i))
                                    {
                                        temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                                    }
                                    else
                                    {
                                        temp = "<A href=\"./Leads.aspx?Order_by_Leads=" + sqlReader.GetName(i) + "\">";
                                        temp += Common_SXD.English_Chinese(sqlReader.GetName(i));
                                        temp += "</A>";
                                    }
                                    break;
                                }
                            case Flag_Database.MySql:
                                {
                                    if (static_sOrder_by_Leads == MysqlReader.GetName(i))
                                    {
                                        temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
                                    }
                                    else
                                    {
                                        temp = "<A href=\"./Leads.aspx?Order_by_Leads=" + MysqlReader.GetName(i) + "\">";
                                        temp += Common_SXD.English_Chinese(MysqlReader.GetName(i));
                                        temp += "</A>";
                                    }
                                    break;
                                }

                        }*/
                        
                    }
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("修改"));
                    row.Cells.Add(cell);
                }
                Table_Leads.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
/*            bool bRead = false;
            switch (Common_SXD.get_Flag_Database())
            {
                case Flag_Database.MSSql:
                    {
                        bRead = sqlReader.Read();
                        break;
                    }
                case Flag_Database.MySql:
                    {
                        bRead = MysqlReader.Read();
                        break;
                    }

            }*/
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series_Lead = sqlReader.GetInt64(0).ToString().Trim();
#elif MySql
                String sNo_Series_Lead = MysqlReader.GetInt64(0).ToString().Trim();
#endif          ///
                /*
                switch (Common_SXD.get_Flag_Database())
                {
                    case Flag_Database.MSSql:
                        {
                            sNo_Series_Lead = sqlReader.GetInt64(0).ToString().Trim();
                            break;
                        }
                    case Flag_Database.MySql:
                        {
                            sNo_Series_Lead = MysqlReader.GetInt64(0).ToString().Trim();
                            break;
                        }

                }*/
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    /*
                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReadOnly:" +
                        sNo_Series_Lead.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series_Lead.Trim() +
                        "</a>"));*/
                    cell.Controls.Add(new LiteralControl("<a>" +
                        sNo_Series_Lead.Trim() +
                        "</a>"));

                    row.Cells.Add(cell);
                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    /*
                    switch (Common_SXD.get_Flag_Database())
                    {
                        case Flag_Database.MSSql:
                            {
                                cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                                break;
                            }
                        case Flag_Database.MySql:
                            {
                                cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
                                break;
                            }

                    }*/
                    
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    if (sDDList_Operator == "前台" && bIs_Receptionist)
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=EditReceptionist:" +
                             sNo_Series_Lead.Trim() +
                             "',900,620);\">客户登记</a>"));
                        row.Cells.Add(cell);

                    }
                    else if (sDDList_Operator == "客服")
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromFrontlineAgent:" +
                             sNo_Series_Lead.Trim() +
                             "',900,620);\">二次分配</a>"));
                        row.Cells.Add(cell);
                    }
                    else if (sDDList_Operator == "顾问" || sDDList_Operator == "一次顾问" || sDDList_Operator == "二次顾问")
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromCounsellor:" +
                             sNo_Series_Lead.Trim() +
                             "',900,620);\">客户状态</a>"));
                        row.Cells.Add(cell);

                    }
                    else if (sDDList_Operator == "已签约" && (bIs_O_M || bIs_Counsellor))
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReadOnly:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                        if (bIs_O_M || bIs_Counsellor)
                            row.Cells.Add(cell);

                    }
                    else if (sDDList_Operator == "延续跟进")
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=Continue_Follow_up:" +
                             sNo_Series_Lead.Trim() +
                             "',900,620);\">延续跟进</a>"));
                        row.Cells.Add(cell);
                    }
                    else if (sDDList_Operator == "地区经理")
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                        row.Cells.Add(cell);

                    }
                    else if (sDDList_Operator == "市场专员" || sDDList_Operator == "有效且非名单")
                    {///客户接触方式="名单（教育展、讲座、市场部批量）"时，市场专员可以存取“客户信息”
                     ///
#if MSSql
                        if (sqlReader.GetValue(8).ToString() == "名单（教育展、讲座、市场部批量）")
                        {
                              cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                   sNo_Series_Lead.Trim() +
                                   "',900,620);\">客户信息</a>"));
                              row.Cells.Add(cell);
                        }
#elif MySql
                        if (MysqlReader.GetValue(8).ToString() == "名单（教育展、讲座、市场部批量）")
                        {
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                            row.Cells.Add(cell);
                        }
                        else
                        {
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=EditMarketing:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                            row.Cells.Add(cell);
                        }
                        

#endif
                        /*
                        switch (Common_SXD.get_Flag_Database())
                        {
                            case Flag_Database.MSSql:
                                {
                                    if (sqlReader.GetValue(8).ToString() == "名单（教育展、讲座、市场部批量）")
                                    {
                                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                             sNo_Series_Lead.Trim() +
                                             "',900,620);\">客户信息</a>"));
                                        row.Cells.Add(cell);
                                    }
                                    break;
                                }
                            case Flag_Database.MySql:
                                {
                                    if (MysqlReader.GetValue(8).ToString() == "名单（教育展、讲座、市场部批量）")
                                    {
                                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                             sNo_Series_Lead.Trim() +
                                             "',900,620);\">客户信息</a>"));
                                        row.Cells.Add(cell);
                                    }
                                    break;
                                }

                        }*/
                        
                    }
                    else if (sDDList_Operator == "0无效" || sDDList_Operator == "休眠" )
                    {
                        if (bIs_O_M)
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                        else if (bIs_Counsellor)
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromCounsellor:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                        
                        if (bIs_O_M || bIs_Counsellor)
                            row.Cells.Add(cell);

                    }
                    else if ( sDDList_Operator == "有效")
                    {
                        if(bIs_O_M)
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromO_M:" +
                                 sNo_Series_Lead.Trim() +
                                 "',900,620);\">客户信息</a>"));
                        else if(bIs_Counsellor)
                            cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReplyFromCounsellor:" +
                                                         sNo_Series_Lead.Trim() +
                                                         "',900,620);\">客户状态</a>"));
                        if(bIs_O_M || bIs_Counsellor)
                            row.Cells.Add(cell);

                    }
                }

                Table_Leads.Rows.Add(row);
                j++;
                /*
                switch (Common_SXD.get_Flag_Database())
                {
                    case Flag_Database.MSSql:
                        {
                            bRead = sqlReader.Read();
                            break;
                        }
                    case Flag_Database.MySql:
                        {
                            bRead = MysqlReader.Read();
                            break;
                        }

                }*/
                
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
            
        }

        /// <summary>
        /// 显示统计结果
        /// </summary>
        private void Choice_Statistics(string myOffice)
        {
            ////////////////
            /// 选定统计表Y轴
            /// 客户了解渠道
            if (CheckBox_Channel_Customer2Know.Checked)
            {


                ///选定统计表X轴
                ///
                if (CheckBox_Office_Statistic.Checked)
                {///地区
                    Display_Statistics(myOffice, 
                        "Office", "Office", "Code_Office",
                        "Channel_Customer2Know", "Channel_Customer2Know", "Code_Channel_Customer2Know");

                }

                if (CheckBox_Type_Contact.Checked)
                {///接触方式
                    Display_Statistics(myOffice,
                        "Type_Contact", "Type_Contact", "Code_Type_Contact",
                        "Channel_Customer2Know", "Channel_Customer2Know", "Code_Channel_Customer2Know");

                }

                if (CheckBox_Overseas_Study.Checked)
                {///留学意向
                    Display_Statistics(myOffice,
                        "Overseas_Study", "Overseas_Study", "Code_Overseas_Study",
                        "Channel_Customer2Know", "Channel_Customer2Know", "Code_Channel_Customer2Know");

                }

                if (CheckBox_State_Customer.Checked)
                {///一次客户状态
                    Display_Statistics(myOffice,
                        "State_Customer", "State_Customer", "Code_State_Customer",
                        "Channel_Customer2Know", "Channel_Customer2Know", "Code_Channel_Customer2Know");

                }

                if (CheckBox_Second_State_Customer.Checked)
                {///二次客户状态
                    Display_Statistics(myOffice,
                        "State_Customer", "State_Customer", "Code_State_Customer",
                        "Channel_Customer2Know", "Channel_Customer2Know", "Code_Channel_Customer2Know");

                }


            }

            ////////////////
            /// 选定统计表Y轴
            /// 地区
            /// 
            if (CheckBox_Office_Statistic.Checked)
            {


                ///选定统计表X轴
                ///
                if (CheckBox_Type_Contact.Checked)
                {///接触方式
                    Display_Statistics(myOffice,
                        "Type_Contact", "Type_Contact", "Code_Type_Contact",
                        "Office", "Office", "Code_Office");

                }

                if (CheckBox_Overseas_Study.Checked)
                {///留学意向
                    Display_Statistics(myOffice,
                        "Overseas_Study", "Overseas_Study", "Code_Overseas_Study",
                        "Office", "Office", "Code_Office");

                }

                if (CheckBox_State_Customer.Checked)
                {///一次客户状态
                    Display_Statistics(myOffice,
                        "State_Customer", "State_Customer", "Code_State_Customer",
                        "Office", "Office", "Code_Office");

                }
            }

            ////////////////
            /// 选定统计表Y轴
            /// 客户接触方式
            /// 
            if (CheckBox_Type_Contact.Checked)
            {


                ///选定统计表X轴
                ///
                
                if (CheckBox_Overseas_Study.Checked)
                {///留学意向
                    Display_Statistics(myOffice,
                        "Overseas_Study", "Overseas_Study", "Code_Overseas_Study",
                        "Type_Contact", "Type_Contact", "Code_Type_Contact");

                }

                if (CheckBox_State_Customer.Checked)
                {///一次客户状态
                    Display_Statistics(myOffice,
                        "State_Customer", "State_Customer", "Code_State_Customer",
                        "Type_Contact", "Type_Contact", "Code_Type_Contact");
                }
            }

            ////////////////
            /// 选定统计表Y轴
            /// 客户留学意向
            /// 
            if (CheckBox_Overseas_Study.Checked)
            {


                ///选定统计表X轴
                ///
                if (CheckBox_State_Customer.Checked)
                {///一次客户状态
                    Display_Statistics(myOffice,
                        "State_Customer", "State_Customer", "Code_State_Customer",
                        "Overseas_Study", "Overseas_Study", "Code_Overseas_Study");
                }
            }

            ////////////////
            /// 选定统计表Y轴
            /// 一次分配客户状态
            /// 
            if (CheckBox_State_Customer.Checked)
            {


                ///选定统计表X轴
                ///

            }

        }
        /// <summary>
        /// 显示客户表中的内容
        /// </summary>
        private void Init_Table_Leads(string myOffice)
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);

            /// 指定 Office_Leads_Selected 为操作人所属的 Office
            /// 
            Session["Office_Leads_Selected"] = myOffice.ToString().Trim();

            ///控制Email/SMS输入文本框
            ///
            if (RadioButtonList_Email_SMS.SelectedValue.Trim() == "Email")
            {
                TextBox_Email_SMS.Visible = true;
                TextBox_Email_SMS.Enabled = true;
                TextBox_Title_Email.Visible = true;
                TextBox_Title_Email.Enabled = true;
                Label_Title_Email.Text = "Email标题:";
                Label_Email_SMS.Text = "Email内容:";
                TextBox_EmailPwd.Visible = true;
                Bt_OK.Visible = true;
                Label_EmailPwd.Text = "公司邮箱密码";

            }
            else if (RadioButtonList_Email_SMS.SelectedValue.Trim() == "SMS")
            {
                TextBox_Email_SMS.Visible = true;
                TextBox_Email_SMS.Enabled = true;
                TextBox_Title_Email.Visible = false;
                Label_Title_Email.Text = "";
                Label_Email_SMS.Text = "短信内容:";
                TextBox_EmailPwd.Visible = false;
                Bt_OK.Visible = true;
                Label_EmailPwd.Text = "";

            }
            else
            {
                TextBox_Email_SMS.Visible = false;
                TextBox_Email_SMS.Enabled = false;
                TextBox_Title_Email.Visible = false;
                TextBox_Title_Email.Enabled = false;
                Label_Title_Email.Text = "";
                Label_Email_SMS.Text = "";
                TextBox_EmailPwd.Visible = false;
                Bt_OK.Visible = false;
                Label_EmailPwd.Text = "";

            }
        

                        
            if ( (sDDList_Operator.ToString().Trim() == "市场专员") || (sDDList_Operator.ToString().Trim() == "地区经理") )
            {////只有市场专员、地区经理可以看到 统计功能选择项目
                CheckBox_Channel_Customer2Know.Visible = true;
                ///Drop_Channel_Customer2Know.Visible = true;
                CheckBox_Office_Statistic.Visible = true;
                ///Drop_Office_Statistic.Visible = true;
                CheckBox_Type_Contact.Visible = true;
                ///Drop_Type_Contact.Visible = true;
                CheckBox_Overseas_Study.Visible = true;
                ///Drop_Overseas_Study.Visible = true;
                CheckBox_State_Customer.Visible = true;
                ///Drop_State_Customer.Visible = true;
                CheckBox_Second_State_Customer.Visible = true;
                ///Drop_Second_State_Customer.Visible = true;

                /// 两项选中，转为显示统计结果
                /// 
                int flag = 0;
                if (CheckBox_Channel_Customer2Know.Checked)
                    flag++;
                if (CheckBox_Office_Statistic.Checked)
                    flag++;
                if (CheckBox_Type_Contact.Checked)
                    flag++;
                if (CheckBox_Overseas_Study.Checked)
                    flag++;
                if (CheckBox_State_Customer.Checked)
                    flag++;
                if (CheckBox_Second_State_Customer.Checked)
                    flag++;

                if (flag == 0)
                {/// 显示客户清单
                    List_Customers(myOffice);

                }
                else if (flag == 2)
                {/// 显示统计结果
                    Choice_Statistics(myOffice);

                }
                else
                {
                    Response.Write("<li>统计表选项错误！只能选择2项！</li>" );
                }

            }
            else
            {
                Off_Display_Controls_Statistics();

                /// 显示客户清单
                List_Customers(myOffice);
            }

           

        }

        protected void Bt_OK_Click(object sender, EventArgs e)
        {

        }
    }
}
